<?php
/**
 * product class. Provide all utilities function related to 
 * product table.
 * <u>Note:</u> all $db param in first parameter is ezMySQL object.
 * See ezSQL documentation for more information.
 */
class product 
{
    function product()
    {
    }
    
    /**
     * Validate product data
     */
    function validateProduct()
    {
    	$msg = "";	
    	
		if($_POST['product_name'] == "")
		{
			if($msg != "") $msg .= "<br />";
			$msg .= _("Missing model name.");
		}

		return $msg;
    }
 
    /**
     * Save product.
     * product is array with format. See smarty template file of product for
     * form's element name.
     * Ex: [cat_parent] => [model_number] => [product_name] => [product_image] => [unit_cost] => [description] => }
     */
    function saveProduct($db, $product)
    {
    	$res = $db->query(sprintf(
					"INSERT INTO %s(category_id, price, product_image, status, product_date_added,pro_active) " .
					"VALUES('%s','%s', '%s', '%s' , now(), '%s')",
    				TBL_PRODUCT, 
    				$product['cat_parent'],
					$product['price'],
    				$product['product_image'],
    				$product['product_status'],
					$product['active']));
    	$id = $db->insert_id;
		$country = $db->get_results("SELECT * FROM ".TBL_LANG);	
		foreach($country as $item){	
    		$db->query(sprintf(
					"INSERT INTO %s(product_id, product_name,product_name_img ,short_info,description,lang_id) " .
					"VALUES('%s', '%s', '%s','%s', '%s' , '%s')",
    				TBL_PRODUCT_LANG, 
    				$id,
					htmlentities($product['product_name'], ENT_QUOTES),
					$product['product_name_img'],
					htmlentities($product['short_info'], ENT_QUOTES),
					htmlentities($product['description'], ENT_QUOTES),
					$item->lang_key));
		}			

    	if($res == true)
    		return $id;
    	else
    		return -1;
    }
    
	 /**
     * Update a product.
     * @param $product is the $_POST param send by HTML form. 
     * See product HTML form in smarty template file for more about
     * form element' name.
     * @return true if query is execute successfully. Otherwise return false.
     */
	function updateProduct($db, $product)
    {
		$product_cap = $product['short_info'];
		$product_des = $product['description'];
		if($product['product_image']!="")
			$cond = "product_image	= '$product[product_image]', ";
		$locale = $_SESSION["LC_ALL"];
		$sql = sprintf(
					"UPDATE %s SET " .
					"product_name 	= '".htmlentities($product['product_name'], ENT_QUOTES)."', " .
					"product_name_img		= '".$product['product_name_img']."', " .
					"short_info		= '%s', " .
					"description	= '%s' " .
					"WHERE product_id = " . $product['id'] ." AND lang_id = '$locale'",
    				TBL_PRODUCT_LANG, htmlentities($product_cap, ENT_QUOTES), htmlentities($product_des, ENT_QUOTES));
		$db->query($sql);			
		$res = $db->query(sprintf(
					"UPDATE %s SET " .
					"category_id 	= '".$product['cat_parent']."', " .
					"price 	= '".$product['price']."', " .
					"pro_active 	= '".$product['active']."', " .
					$cond .
					"status			= '$product[product_status]', " .
					"product_date_modified	= now() " .
					"WHERE product_id = " . $product['id'],
    				TBL_PRODUCT));
					
    	return $res;
    }
	/**
     * Save photos of product.
     */
    function saveProductPhotos($db, $productId,$product_photo) {
    	$res = $db->query(sprintf(
					"INSERT INTO %s(product_id, product_photo, product_date_create) " .
					"VALUES(%s, '%s', now())",
    				TBL_PRODUCT_PHOTOS, 
    				$productId,
    			    $product_photo));
		if($res == true)
    		return $id = $db->insert_id;
    	else
    		return -1;
		
    }
	
	 /**
     * Delete a product with inputted id.
     * @param $id the product's id.
     * @return true if delete successfully otherwise return false.
     */
    function deleteProduct($db, $id)
	{
		//Delete product
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_PRODUCT);
		$db->query(sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_PRODUCT_LANG));
    	$res = $db->query($sql);
    	return $res;
	}
	
	
    /**
     * Get all products in database.
     * @return The list of product object.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getAllProduct($db,$langid)
    {
    	$sql = sprintf("SELECT %2\$s.cat_name, %1\$s.* FROM %1\$s ".
				"left join %2\$s on %1\$s.category_id = %2\$s.cat_ID ".
				" LEFT JOIN %3\$s ON %3\$s.cat_id = %2\$s.cat_ID AND %3\$s.lang_id = '$langid'". 
				" LEFT JOIN %4\$s ON %4\$s.product_id = %1\$s.product_id AND %4\$s.lang_id = '$langid'", 
    			TBL_PRODUCT,
    			TBL_CATEGORY,
				TBL_CATEGORY_LANG,
				TBL_PRODUCT_LANG);
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
	
	function getAllProducts($db,$langid)
    {
    	$sql = "SELECT p.product_id,t.product_name FROM ".TBL_PRODUCT." p ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid'";
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
	
	 function countgetAllProducts($db,$langid)
    {
    	$sql = "SELECT COUNT(p.product_id) as total FROM ".TBL_PRODUCT." p ".
				"INNER JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res->total;
    }
	
	function getAllProductsSharePageAdmin($db,$langid,$maxview,$path)
	{
		$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $maxview;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
	
		$strSQL = "SELECT p.*,FORMAT(p.price,0) as price,t.product_name,t.short_info,t.description,t.lang_id,l.cat_name,c.* FROM ".TBL_PRODUCT." p ".
					"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
					" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
					" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
					"ORDER BY p.product_id ASC LIMIT $page_start, $rows_per_page";
		$res = $db->get_results($strSQL);
		if($res!=""){
				foreach($res as $item){
					$item->product_name = html_entity_decode($item->product_name);
					$item->cat_name = html_entity_decode($item->cat_name);
					$item->description = html_entity_decode($item->description);
				}
			}
		return $res;
	}
    
	function getImageProductByCat($db,$cat_id)
    {
    	$sql = sprintf("SELECT product_id,product_image FROM ".TBL_PRODUCT." WHERE category_id = '$cat_id'");
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
	
    
    function getAllProductsSearchSharePage($db ,$txtKey,$langid,$share,$path)
    {
    	$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $share;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
    	$sql = "SELECT * FROM ".TBL_PRODUCT." p LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$locale' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE t.product_name like '%%$txtKey%%' ORDER BY t.product_id ASC LIMIT $page_start, $rows_per_page";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
    
    /**
     * Get a product with inputted product id.
     * @param $id id of the product.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getProduct($db, $id)
    {
		$locale = $_SESSION["LC_ALL"];
    	$sql = "SELECT p.*, l.cat_name,t.* FROM ".TBL_PRODUCT." p LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$locale' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$locale' ".
				"WHERE p.product_id='$id'";
    	$res = $db->get_row($sql);
    	if($res!=""){
					$res->cat_name = html_entity_decode($res->cat_name, ENT_QUOTES);
					$res->product_name = html_entity_decode($res->product_name, ENT_QUOTES);
					$res->short_info = html_entity_decode($res->short_info, ENT_QUOTES);
					$res->description = html_entity_decode($res->description, ENT_QUOTES);
			}
    	return $res;
    }
    
    function getProductNameById($db, $id)
    {
    	$sql = "SELECT product_name FROM ".TBL_PRODUCT." WHERE product_id='$id'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res;
    }
    
    function getProductCategory($db, $id)
    {
    	$sql = "SELECT category_id FROM ".TBL_PRODUCT." WHERE product_id='$id'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res->category_id;
    }
    
    function getCategoryName($db, $id)
    {
    	$sql = "SELECT cat_name FROM ".TBL_CATEGORY." WHERE cat_ID='$id'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res->cat_name;
    }
    
   
    /**
     * Get all product with inputted category id.
     * @param $id the category id.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
	function count_product($db, $id,$langid,$status,$productid){
		if($id!="")
			$cond = " AND p.category_id='$id'";
		if($status!="")
			$cond_2 = " AND p.status 	='$status'";
		if($productid!="")
			$cond_3 = " AND p.product_id!='$productid'";	
    	$sql = "SELECT COUNT(p.product_id) as total FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$locale' ".
				"WHERE l.lang_id = '$langid' $cond $cond_2 $cond_3";
    			
    	$res = $db->get_row($sql);
    	
    	return $res->total;
    } 
	 
    function getProductsByCategory($db, $id,$langid,$share,$path,$status,$productid){
		if($id!="")
			$cond = " AND p.category_id='$id'";
		if($status!="")
			$cond_2 = " AND p.status 	='$status'";
		if($productid!="")
			$cond_3 = " AND p.product_id!='$productid'";		
		$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $share;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
    	$sql = sprintf("SELECT * FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$locale' ".
				"WHERE l.lang_id = '$langid' $cond $cond_2 $cond_3 LIMIT $page_start, $page_end");
    			
    	$ressult = $db->get_results($sql);
    	return $ressult;
    }
    
    
	/**
     * Get a photos with inputted product id.
     * @param $id id of the product.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function PhotosByProductId($db, $id) {
    	$sql = "SELECT * FROM " . TBL_PRODUCT_PHOTOS . " p ".
				" WHERE p.product_id='$id'";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
	
	function deletePhotoByProductID($db, $id) {
		//Delete product
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_PRODUCT_PHOTOS);
		
		
    	$res = $db->query($sql);
  	
    	return $res;
	}
	
	function deletePhotoProduct($db, $id) {
		//Delete product
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.id = '$id'", 
				TBL_PRODUCT_PHOTOS);
		
		
    	$res = $db->query($sql);
  	
    	return $res;
	}
    
}
?>